/**
 * 
 */
package com.h819.core.file.excel.poi;

import com.h819.core.commons.MyLangUtils;
import com.h819.core.utils.MyDateUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.extractor.ExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.Locale;

import static java.lang.System.out;

/**
 * @ClassName: POIBase
 * @Description: TODO(POI 辅助工具类)
 * @author h819
 * @date May 09, 2009 3:15:28 PM
 * @version V1.0
 */

public class POIBase {

	private static Logger log = Logger.getLogger(POIBase.class);

	// 为了防止 excel 单元格内容和分隔符混淆，分隔符设计的复杂一些
	// 每行中，行号和第一列之间的分隔符
	public static final String spliteLineTag = ":";
	// 列分隔符
	public static final String spliteColumTag = "|;|";
	// 数值等于分隔符
	public static final String spliteValueTag = "|=|";

	/**
	 * 转换 cell 内容为 String ，忽略 cell 格式
	 * 
	 * @param cell
	 * @return
	 */
	public static String convertCellToStringValue(Cell cell) {
		String valueTemp = "";
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			valueTemp = cell.getRichStringCellValue().getString();
			break;

		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				valueTemp = MyDateUtils.toDateTime(cell.getDateCellValue())
						.toString("yyyy-MM-dd ", Locale.CHINA);
			} else {
				valueTemp = new Double(cell.getNumericCellValue()).toString();
			}

			break;

		case Cell.CELL_TYPE_BOOLEAN:
			valueTemp = new Boolean(cell.getBooleanCellValue()).toString();
			break;

		case Cell.CELL_TYPE_FORMULA:
			valueTemp = cell.getCellFormula();
			break;

		default:
			valueTemp = "";
		}

		// 去掉首尾空格
		valueTemp = MyLangUtils.trim(valueTemp);
		// excel 中，经常会有换行符，去掉回车、换行、制表符
		valueTemp = MyLangUtils.removeReturn(valueTemp);

		return valueTemp;
	}

	/**
	 * 利用 POI 提供的工具，提取文件内容为字符串
	 * 
	 * @param excelFile
	 *            待提取的 excel 文件
	 * @return
	 */
	public static String excelExtractor(File excelFile) {

		try {
			HSSFWorkbook wb = new HSSFWorkbook(new FileInputStream(excelFile));
			ExcelExtractor extractor = new ExcelExtractor(wb);
			extractor.setFormulasNotResults(true);
			extractor.setIncludeSheetNames(true);
			return extractor.getText();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}

	/**
	 * 用来解析行数据，得到指定的列值。此函数仅针对 POIUtils.java 生成 的 set 中的行结构有效
	 * 
	 * @param lineValueString
	 *            行字符串值
	 * @param columNum
	 *            指定列序号
	 * @return
	 */
	public static String getColumValue(String lineValueString, int columNum) {

		// 例子

		// File=1006.xls|;|1004:0|=|ISO 11298-1-2010|;|2|=|Plastics piping
		// systems|;|3|=|ISO|;|4|=|A|;|5|=|2010-02-23|;|6|=|2010-02-23|;|9|=|23.040.20;23.040.45;93.025|;|16|=|EN|;|17|=|24|;|18|=|ISO
		// 11298-1:2010 specifies the
		// requirements.|;|19|=|2010-06-24|;|20|=|ISO\ISO11298-1Y2010.pdf

		// 去掉无关信息：文件名+列分隔符
		lineValueString = StringUtils.substringAfter(lineValueString,
				spliteColumTag);

		// 构造临时变量：列分割符+列号+值分隔符, |;|1|=|
		String strTemp = spliteColumTag + new Integer(columNum).toString()
				+ spliteValueTag;

		// 构造第一列 :0|=|
		String strTemp0 = spliteLineTag + new Integer(columNum).toString()
				+ spliteValueTag;

		String columValue = "";

		if (StringUtils.contains(lineValueString, strTemp0)) {// 取第一列值
			columValue = StringUtils.substringAfter(lineValueString, strTemp0);
			columValue = StringUtils
					.substringBefore(columValue, spliteColumTag);
			return columValue;

		}

		if (StringUtils.contains(lineValueString, strTemp)) {
			columValue = StringUtils.substringAfter(lineValueString, strTemp);
			columValue = StringUtils
					.substringBefore(columValue, spliteColumTag);

			return columValue;

		}

		else // 不包含指定列
		{
			// log.info("the value does not contains the " + columNum +
			// " colum");
			return null;
		}

	}

	/**
	 * 替换行的指定列值
	 * 
	 * @param oldLineValueString
	 *            行字符串值
	 * @param columNum
	 *            指定列号
	 * @param newColumValue
	 *            新列值
	 * @return 替换后的行字符串
	 */
	public static String replaceColumValue(String oldLineValueString,
			int columNum, String newColumValue) {

		// 临时变量：列分割符+列号+值分隔符, 如 "|;|1|=|"
		String strTemp = spliteColumTag + new Integer(columNum).toString()
				+ spliteValueTag;

		// 临时仅含一列的情况，如 ":0|=|"
		String strTemp0 = spliteLineTag + new Integer(columNum).toString()
				+ spliteValueTag;

		String oldColumValue = getColumValue(oldLineValueString, columNum);

		// log.info("strTemp " + strTemp);
		// log.info("strTemp0 " + strTemp0);
		// log.info("oldColumValue " + oldColumValue);

		// 多列情况
		if (StringUtils.contains(oldLineValueString, strTemp + oldColumValue)) {

			return StringUtils.replace(oldLineValueString, strTemp
					+ oldColumValue, strTemp + newColumValue);
		}

		// 一列的情况
		else if (StringUtils.contains(oldLineValueString, strTemp0
				+ oldColumValue)) {
			return StringUtils.replace(oldLineValueString, strTemp0
					+ oldColumValue, strTemp0 + newColumValue);
		}

		else // 不包含指定列，在最后附加此列信息
		{
			return oldLineValueString + spliteColumTag
					+ new Integer(columNum).toString() + spliteValueTag
					+ newColumValue;
		}

	}

	/**
	 * 用来解析行数据，得到文件名称。此函数仅针对 POIUtils.java 生成 的 set 中的行结构有效
	 * 
	 * @param lineValueString
	 *            行字符串值
	 * @return
	 */
	public static String getFileName(String lineValueString) {

		return StringUtils.substringBetween(lineValueString, "File=",
				POIUtils.spliteColumTag);

	}

	// jxl/write/biff/RowRecord.java.html
	// 上面的类定义了，最大列号为 256
	/**
	 * 通过列号，返回列名称 列数从 0 开始计算 仅能用于列数 AA -> ZZ 26*26= 676
	 * 
	 * @param columNumber
	 *            列数，小于 675
	 * @return
	 */
	public static String getCloumNameByColumNumber(int columNumber) {
		String[] table = new String[] { "A", "B", "C", "D", "E", "F", "G", "H",
				"I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T",
				"U", "V", "W", "X", "Y", "Z" };

		// 打印测试
		// int i = 0;
		// for (String s : table) {
		// // log.info(i + " " + s);
		// i++;
		// }

		String temp = "";
		int a = columNumber / 25;// 取整
		int b = columNumber % 25;// 取余数

		if (columNumber < 25)// 小于 25
			temp = table[columNumber];
		else if (columNumber == 25) // 等于 25
			temp = "Z";
		else { // 大于 25
			if (b != 0) { // 不是 25 的倍数
				temp = table[a - 1] + table[b - 1];
			} else {// 是 25 的倍数
				temp = table[a - 2] + "Z";
			}
		}
		return temp;
	}

	/**
	 * 判断给定的行是否为空白行
	 * 
	 * @param row
	 *            指定行
	 * @return
	 */
	public static boolean isBlankRow(Row row) {

		boolean isBlank = true;
		for (Cell cell : row) {// 循环列
			// 跳过空白 cell
			if (cell.getCellType() == cell.CELL_TYPE_BLANK)
				continue;
			isBlank = false;
		}
		return isBlank;
	}

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		// System.out.println(" 0 ==========");
		// POIBase.getCloumNameByColumNumber(0);
		// System.out.println(" 9 ==========");
		// POIBase.getCloumNameByColumNumber(9);
		// System.out.println(" 25 ==========");
		// POIBase.getCloumNameByColumNumber(25);
		// System.out.println(" 26 ==========");
		// POIBase.getCloumNameByColumNumber(26);
		// System.out.println(" 27 ==========");
		// POIBase.getCloumNameByColumNumber(27);
		// System.out.println(" 50 ==========");
		// POIBase.getCloumNameByColumNumber(50);
		// System.out.println(" 51 ==========");
		// POIBase.getCloumNameByColumNumber(51);
		// System.out.println(" 74 ==========");
		// POIBase.getCloumNameByColumNumber(74);
		// System.out.println(" 75 ==========");
		// POIBase.getCloumNameByColumNumber(75);
		// System.out.println(" 100==========");
		// POIBase.getCloumNameByColumNumber(100);

		// 测试
		// String sTemp =

		String sTemp = "File=1006.xls|;|1004:0|=|ISO 11298-1-2010|;|2|=|Plastics piping systems|;|3|=|ISO|;|4|=|A|;|5|=|2010-02-23|;|6|=|2010-02-23|;|9|=|23.040.20;23.040.45;93.025|;|16|=|EN|;|17|=|24|;|18|=|ISO 11298-1:2010 specifies the requirements.|;|19|=|2010-06-24|;|20|=|ISO\\ISO11298-1Y2010.pdf";

		out.println("0: " + POIBase.getColumValue(sTemp, 0));
		out.println("1: " + POIBase.getColumValue(sTemp, 1));
		out.println("2: " + POIBase.getColumValue(sTemp, 2));
		out.println("3: " + POIBase.getColumValue(sTemp, 3));
		out.println("4: " + POIBase.getColumValue(sTemp, 4));
		out.println("5: " + POIBase.getColumValue(sTemp, 5));
		out.println("6: " + POIBase.getColumValue(sTemp, 6));
		out.println("7: " + POIBase.getColumValue(sTemp, 7));
		out.println("8: " + POIBase.getColumValue(sTemp, 8));
		out.println("9: " + POIBase.getColumValue(sTemp, 9));
		out.println("10: " + POIBase.getColumValue(sTemp, 10));
		out.println("11: " + POIBase.getColumValue(sTemp, 11));
		out.println("12: " + POIBase.getColumValue(sTemp, 12));
		out.println("13: " + POIBase.getColumValue(sTemp, 13));
		out.println("14: " + POIBase.getColumValue(sTemp, 14));
		out.println("15: " + POIBase.getColumValue(sTemp, 15));
		out.println("16: " + POIBase.getColumValue(sTemp, 16));
		out.println("17: " + POIBase.getColumValue(sTemp, 17));
		out.println("18: " + POIBase.getColumValue(sTemp, 18));
		out.println("19: " + POIBase.getColumValue(sTemp, 19));
		out.println("20: " + POIBase.getColumValue(sTemp, 20));
		out.println("21: " + POIBase.getColumValue(sTemp, 21));
		out.println("22: " + POIBase.getColumValue(sTemp, 22));
		out.println("23: " + POIBase.getColumValue(sTemp, 23));
		out.println("24: " + POIBase.getColumValue(sTemp, 24));
		out.println("25: " + POIBase.getColumValue(sTemp, 25));

		// 测试替换
		out.println("replaceColumValue(0): "
				+ POIBase.replaceColumValue(sTemp, 0, "EF"));
		out.println("replaceColumValue(6): "
				+ POIBase.replaceColumValue(sTemp, 6, "EF"));
		out.println("replaceColumValue(20): "
				+ POIBase.replaceColumValue(sTemp, 20, "EF"));

	}
}
